Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
'Imports Office = Microsoft.Office.Core
Imports System.Runtime.InteropServices
Imports System.Drawing
Imports System.Text.RegularExpressions

Public Class TemplateFormula
    Implements ITemplateFormula

    Private initialFormula As String
    Private _location As Excel.Range

    Public Sub New(ByRef location As Excel.Range)
        Me._location = location
        InitReference(location.Formula.ToString())
        Me.initialFormula = location.Formula.ToString()
    End Sub

    Private Sub InitReference(ByVal formula As String)
        Dim match As Match = Regex.Match(formula, "\w+\d+")
        While (match.Success)
            If (Not match.Value = String.Empty) Then
                reference += match.Value & ";"
                match = match.NextMatch
            End If
        End While
        reference = reference.TrimEnd(";"c)
    End Sub

    Public Sub PreDeployFormula(ByVal rowCount As Integer) Implements ITemplateFormula.PreDeployFormula
        Dim i As Integer
        For i = 1 To rowCount - 1
            _location.Offset(i, 0).Insert(Excel.XlDirection.xlDown, True)
        Next
        Me._rowCount = rowCount
        Me._isPreDeployed = True
    End Sub

    Public Sub DeployFormula() Implements ITemplateFormula.DeployFormula
        _location.Formula = initialFormula

        Dim fillRange As Excel.Range = _location.Worksheet.Range( _
            _location, _location.Offset(RowCount - 1, 0))
        Try
            _location.AutoFill(fillRange)
        Catch ex As Exception
        End Try

        _isDeployed = True
    End Sub

    Private reference As String
    Public ReadOnly Property ReferenceAddress() As String Implements ITemplateFormula.ReferenceAddress
        Get
            Return Me.reference
        End Get
    End Property


    Public Sub ReleaseFormula() Implements ITemplateFormula.ReleaseFormula
        ReleaseComObject(_location)
    End Sub

    Public Function HasIntersection(ByRef range As Microsoft.Office.Interop.Excel.Range) As Boolean Implements ITemplateFormula.HasIntersection
        Try
            Dim o As Object = range.Application.Range(range.AddressLocal & " " & ReferenceAddress).Value
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Overloads Function HasIntersection1(ByVal address As String) As Boolean Implements ITemplateFormula.HasIntersection
        Dim o As Object
        Try
            Dim range As Microsoft.Office.Interop.Excel.Range = _location.Application.Range(address & " " & ReferenceAddress)
            Try
                o = range.Value
            Finally
                ReleaseComObject(range)
            End Try
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function

    Public Overloads Function IsReferenceOnMe(ByVal areaReference As String) As Boolean Implements ITemplateFormula.IsReferenceOnMe
        Dim o As Object
        Try
            Dim range As Microsoft.Office.Interop.Excel.Range = _location.Worksheet.Range(areaReference & " " & _location.AddressLocal)
            Try
                o = range.Value
            Finally
                ReleaseComObject(range)
            End Try
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Private _rowCount As Integer = -1
    Public ReadOnly Property RowCount() As Integer Implements ITemplateFormula.RowCount
        Get
            Return Me._rowCount
        End Get
    End Property

    Private _isDeployed As Boolean = False
    Public ReadOnly Property IsDeployed() As Boolean Implements ITemplateFormula.IsDeployed
        Get
            Return Me._isDeployed
        End Get
    End Property

    Private _isPreDeployed As Boolean = False
    Public ReadOnly Property IsPreDeployed() As Boolean Implements ITemplateFormula.IsPreDeployed
        Get
            Return Me._isPreDeployed
        End Get
    End Property

End Class
